{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_of_lat_long = [[33.73, 75.15], [34.02, 74.78], [34.42, 74.65],\n",
    "                    [34.198, 74.3636], [33.145733, 75.547817], [34.23, 74.78],\n",
    "                    [32.73, 74.87], [34.55, 76.133333], [32.583333, 75.5],\n",
    "                    [33.312683, 75.769447], [33.64, 75.02], [34.52, 74.25],\n",
    "                    [34.16, 77.58], [33.77, 74.1], [33.873538, 74.899019],\n",
    "                    [33.25, 74.25], [33.24, 75.24], [32.55, 75.116667],\n",
    "                    [33.72, 74.83], [34.083333, 74.833333], [33, 75.166667]]\n",
    "len(list_of_lat_long)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_data = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['anantnag.csv', 'badgam.csv', 'bandipur.csv', 'baramula.csv', 'doda.csv', 'ganderbal.csv', 'jammu.csv', 'kargil.csv', 'kathua.csv', 'kishtwar.csv', 'kulgam.csv', 'kupwara.csv', 'leh.csv', 'poonch.csv', 'pulwama.csv', 'rajauri.csv', 'ramban.csv', 'samba.csv', 'shupiyan.csv', 'srinagar.csv', 'udhampur.csv']\n"
     ]
    }
   ],
   "source": [
    "path = os.getcwd()\n",
    "path\n",
    "list_of_files = os.listdir(path)\n",
    "list_of_files\n",
    "csvs = []\n",
    "for x in list_of_files:\n",
    "    if x.endswith(\".csv\"):\n",
    "        csvs.append(x) ## Making a list of file names with their respective filenames\n",
    "csvs = sorted(set(csvs))\n",
    "print(csvs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['anantnag.csv', 'badgam.csv', 'bandipur.csv', 'baramula.csv', 'doda.csv', 'ganderbal.csv', 'jammu.csv', 'kargil.csv', 'kathua.csv', 'kishtwar.csv', 'kulgam.csv', 'kupwara.csv', 'leh.csv', 'poonch.csv', 'pulwama.csv', 'rajauri.csv', 'ramban.csv', 'samba.csv', 'shupiyan.csv', 'srinagar.csv', 'udhampur.csv']\n"
     ]
    }
   ],
   "source": [
    "path = os.getcwd()\n",
    "list_of_files = os.listdir(path)\n",
    "csvs = []\n",
    "\n",
    "for x in list_of_files:\n",
    "    if x.endswith(\".csv\"):\n",
    "        csvs.append(x) ## Making a list of file names with their respective filenames\n",
    "csvs = sorted(set(csvs))\n",
    "print(csvs)\n",
    "i = 0\n",
    "for csv in csvs:\n",
    "    read = pd.read_csv(csv)\n",
    "    read = pd.DataFrame(read)\n",
    "    read['date_time'] = pd.to_datetime(read['date_time'])\n",
    "    read['lat'] = list_of_lat_long[i][0]\n",
    "    read['long'] = list_of_lat_long[i][1] \n",
    "    csv_new = csv.replace(\".csv\", \"\")\n",
    "    csv_new = csv_new.capitalize()\n",
    "    read['area'] = csv_new\n",
    "    final_data = pd.concat([final_data, read], ignore_index=True)\n",
    "    i = i + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above function removes the following column values:\n",
    "<br>\n",
    "'sunHour', 'uvIndex', 'uvIndex.1', 'moon_illumination', 'moonrise',\n",
    "'moonset', 'cloudcover', 'humidity', 'winddirDegree', 'DewPointC', 'FeelsLikeC',\n",
    "'windspeedKmph', 'WindGustKmph', 'sunset', 'sunrise', 'HeatIndexC', 'WindChillC'\n",
    "\n",
    "and compiles a final dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date_time', 'maxtempC', 'mintempC', 'totalSnow_cm', 'sunHour',\n",
       "       'uvIndex', 'uvIndex.1', 'moon_illumination', 'moonrise', 'moonset',\n",
       "       'sunrise', 'sunset', 'DewPointC', 'FeelsLikeC', 'HeatIndexC',\n",
       "       'WindChillC', 'WindGustKmph', 'cloudcover', 'humidity', 'precipMM',\n",
       "       'pressure', 'tempC', 'visibility', 'winddirDegree', 'windspeedKmph',\n",
       "       'lat', 'long', 'area'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "final_data = final_data.rename(columns = {'area': 'district'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = final_data[['date_time', 'district', 'lat', 'long', 'sunHour', 'maxtempC', 'mintempC',\n",
    "            'totalSnow_cm', 'precipMM', 'pressure', 'tempC', 'visibility']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_time</th>\n",
       "      <th>district</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>sunHour</th>\n",
       "      <th>maxtempC</th>\n",
       "      <th>mintempC</th>\n",
       "      <th>totalSnow_cm</th>\n",
       "      <th>precipMM</th>\n",
       "      <th>pressure</th>\n",
       "      <th>tempC</th>\n",
       "      <th>visibility</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-01-01</td>\n",
       "      <td>Anantnag</td>\n",
       "      <td>33.73</td>\n",
       "      <td>75.150000</td>\n",
       "      <td>8.7</td>\n",
       "      <td>4</td>\n",
       "      <td>-7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1024</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-01-02</td>\n",
       "      <td>Anantnag</td>\n",
       "      <td>33.73</td>\n",
       "      <td>75.150000</td>\n",
       "      <td>8.7</td>\n",
       "      <td>4</td>\n",
       "      <td>-4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1023</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-01-03</td>\n",
       "      <td>Anantnag</td>\n",
       "      <td>33.73</td>\n",
       "      <td>75.150000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>-3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1021</td>\n",
       "      <td>-1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-01-04</td>\n",
       "      <td>Anantnag</td>\n",
       "      <td>33.73</td>\n",
       "      <td>75.150000</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-2</td>\n",
       "      <td>-7</td>\n",
       "      <td>0.2</td>\n",
       "      <td>6.6</td>\n",
       "      <td>1019</td>\n",
       "      <td>-2</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-01-05</td>\n",
       "      <td>Anantnag</td>\n",
       "      <td>33.73</td>\n",
       "      <td>75.150000</td>\n",
       "      <td>8.7</td>\n",
       "      <td>3</td>\n",
       "      <td>-8</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.2</td>\n",
       "      <td>1020</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83386</th>\n",
       "      <td>2019-11-11</td>\n",
       "      <td>Udhampur</td>\n",
       "      <td>33.00</td>\n",
       "      <td>75.166667</td>\n",
       "      <td>8.7</td>\n",
       "      <td>25</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1015</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83387</th>\n",
       "      <td>2019-11-12</td>\n",
       "      <td>Udhampur</td>\n",
       "      <td>33.00</td>\n",
       "      <td>75.166667</td>\n",
       "      <td>8.7</td>\n",
       "      <td>27</td>\n",
       "      <td>18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1016</td>\n",
       "      <td>27</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83388</th>\n",
       "      <td>2019-11-13</td>\n",
       "      <td>Udhampur</td>\n",
       "      <td>33.00</td>\n",
       "      <td>75.166667</td>\n",
       "      <td>5.6</td>\n",
       "      <td>27</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1019</td>\n",
       "      <td>27</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83389</th>\n",
       "      <td>2019-11-14</td>\n",
       "      <td>Udhampur</td>\n",
       "      <td>33.00</td>\n",
       "      <td>75.166667</td>\n",
       "      <td>4.0</td>\n",
       "      <td>25</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1020</td>\n",
       "      <td>25</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83390</th>\n",
       "      <td>2019-11-15</td>\n",
       "      <td>Udhampur</td>\n",
       "      <td>33.00</td>\n",
       "      <td>75.166667</td>\n",
       "      <td>4.0</td>\n",
       "      <td>25</td>\n",
       "      <td>18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1019</td>\n",
       "      <td>25</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83391 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       date_time  district    lat       long  sunHour  maxtempC  mintempC  \\\n",
       "0     2009-01-01  Anantnag  33.73  75.150000      8.7         4        -7   \n",
       "1     2009-01-02  Anantnag  33.73  75.150000      8.7         4        -4   \n",
       "2     2009-01-03  Anantnag  33.73  75.150000      7.0        -1        -3   \n",
       "3     2009-01-04  Anantnag  33.73  75.150000      7.0        -2        -7   \n",
       "4     2009-01-05  Anantnag  33.73  75.150000      8.7         3        -8   \n",
       "...          ...       ...    ...        ...      ...       ...       ...   \n",
       "83386 2019-11-11  Udhampur  33.00  75.166667      8.7        25        17   \n",
       "83387 2019-11-12  Udhampur  33.00  75.166667      8.7        27        18   \n",
       "83388 2019-11-13  Udhampur  33.00  75.166667      5.6        27        17   \n",
       "83389 2019-11-14  Udhampur  33.00  75.166667      4.0        25        19   \n",
       "83390 2019-11-15  Udhampur  33.00  75.166667      4.0        25        18   \n",
       "\n",
       "       totalSnow_cm  precipMM  pressure  tempC  visibility  \n",
       "0               0.0       0.0      1024      4          10  \n",
       "1               0.0       0.0      1023      4          10  \n",
       "2               0.0       3.2      1021     -1           8  \n",
       "3               0.2       6.6      1019     -2           6  \n",
       "4               0.2       0.2      1020      3          10  \n",
       "...             ...       ...       ...    ...         ...  \n",
       "83386           0.0       0.0      1015     25          10  \n",
       "83387           0.0       0.0      1016     27          10  \n",
       "83388           0.0       0.0      1019     27          10  \n",
       "83389           0.0       3.8      1020     25          10  \n",
       "83390           0.0       5.8      1019     25           9  \n",
       "\n",
       "[83391 rows x 12 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(r'climate_data.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
